﻿Imports System.Data.SqlClient
Imports Abyx.SVCMS.Library
Imports Abyx.SVCMS.Library.DB

Public Class SqcManager
    Inherits ManagerBase

#Region "Constructors"
    Public Sub New(ByVal LMuser As UserType)
        MyBase.New(LMuser)
    End Sub
#End Region

#Region "CatalogItem"
    Public Function Update_CatalogItem(CatalogItemObject As CatalogItemType) As Boolean

        'Do validation
        If CatalogItemObject.Name = "" Then
            Throw New DataValidationException("Data validation failed, catalog item name cannot be empty")
        End If

        'check if exists do insert else do update
        Dim cmd As New SqlCommand("SELECT COUNT(*) " & _
                          "FROM [SQC_Catalog] " & _
                          "WHERE [EquipmentId]='" & CatalogItemObject.EquipmentId.ToString & "'")
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim trans As SqlTransaction = con.BeginTransaction

            cmd.Connection = con
            cmd.Transaction = trans
            If Utility.GetRecordCount(cmd) > 0 Then
                'do update
                'update catalog item
                With CatalogItemObject
                    .Connection = con
                    .Transaction = trans
                    .LMUserId = Me.LMuser.UserId
                    Me.LMDate = DateTime.Now
                    .LMDate = Me.LMDate
                    .Update()
                End With

                'flush existing features
                cmd.CommandText = "DELETE FROM [SQC_Features] WHERE [EquipmentId]='" & CatalogItemObject.EquipmentId.ToString & "'"
                cmd.ExecuteNonQuery()
            Else
                'do insert
                With CatalogItemObject
                    .Connection = con
                    .Transaction = trans
                    .LMUserId = Me.LMuser.UserId
                    Me.LMDate = DateTime.Now
                    .LMDate = Me.LMDate
                    .Insert()
                End With
            End If

            'insert features
            For Each feature As FeaturesType In CatalogItemObject.Features
                With feature
                    .Connection = con
                    .Transaction = trans
                    .FeatureId = Guid.NewGuid
                    .LMUserId = Me.LMuser.UserId
                    .LMDate = Me.LMDate
                    .EquipmentId = CatalogItemObject.EquipmentId
                    .Insert()
                End With
            Next

            trans.Commit()
            con.Close()
        End Using

        Return True
    End Function
    Public Function Delete_CatalogItem(CatalogItemObject As CatalogItemType) As Boolean

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()

            With CatalogItemObject
                .Connection = con
                .LMUserId = Me.LMuser.UserId
                Me.LMDate = DateTime.Now
                .LMDate = Me.LMDate
                .Delete()
            End With

            con.Close()
        End Using

        Return True
    End Function
    Public Function Get_CatalogItems_All() As CatalogItemTypeCollection
        Dim catalog As New CatalogItemTypeCollection

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()

            With catalog
                .Connection = con
                .Load()
            End With

            con.Close()
        End Using

        Return catalog
    End Function
#End Region

#Region "Manufacturers"
    Public Function Update_Manufacturer(manufacturerObject As ManufacturerType) As Boolean

        'Validate data
        If manufacturerObject.Name = "" Then
            Throw New DataValidationException("Data validation error, manufacturer name cannot be empty")
        End If

        Dim cmd As New SqlCommand("SELECT COUNT(*) " & _
                          "FROM [SQC_Manufacturers] WHERE [ManufacturerId]='" & manufacturerObject.ManufacturerId.ToString & "'")
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()

            Dim trans As SqlTransaction = con.BeginTransaction
            cmd.Connection = con
            cmd.Transaction = trans

            'check if Id already exists
            If Utility.GetRecordCount(cmd) > 0 Then
                'Update this existing record
                manufacturerObject.Connection = con
                manufacturerObject.Transaction = trans
                manufacturerObject.Update()
            Else
                'insert new record
                manufacturerObject.Connection = con
                manufacturerObject.Transaction = trans
                manufacturerObject.Insert()
            End If

            trans.Commit()
            con.Close()
        End Using

        Return True
    End Function
    Public Function Delete_Manufacturer(manufacturerObject As ManufacturerType) As Boolean

        'Do validation

        'Do deletion
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            With manufacturerObject
                .Connection = con
                .Delete()
            End With
            con.Close()
        End Using

        Return True
    End Function
    Public Function Get_Manufacturers_All() As ManufacturerTypeCollection
        Dim manufacturers As New ManufacturerTypeCollection

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            With manufacturers
                .Connection = con
                .DataSorters.Add(ManufacturerTypeFields.Name)
                .Load()
            End With
            con.Close()
        End Using

        Return manufacturers
    End Function
#End Region



End Class